package db.jdbc;

import db.UserRepository;
import domain.Club;
import domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 用户资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcUserRepository implements UserRepository {
    private static final String INSERT_USER = "insert into _user(username,_password,nickname,createTime,gender,college)values(?,?,?,CURRENT_TIMESTAMP,?,?) ";
    private static final String UPDATE_USER = "update _user set nickname=?,telephone=?,email=?,gender=?,college=? where username=?";
    private static final String UPDATE_USER_PASSWD = "update _user set _password=? where username=?";
    private static final String UPDATE_USER_NICKNAME = "update _user set nickname=? where username=?";
    private static final String UPDATE_USER_TELEPHONE = "update _user set telephone=? where username=?";
    private static final String UPDATE_USER_EMAIL = "update _user set email=? where username=?";
    private static final String UPDATE_USER_GENDER = "update _user set gender=? where username=?";
    private static final String UPDATE_USER_COLLEGE = "update _user set college=? where username=?";

    private static final String SELECT_USER = "select * from _user";
    private static final String SELECT_USER_Pagination = SELECT_USER + " order by userID  limit ? offset  ?";
    private static final String DELETE_USER_by_username = "delete from _user where username = ?";
    private static final String DELETE_USER_by_userID = "delete from _user where userID = ?";

    private static final String DELETE_club_users = "delete from club_users where userID = ?";
    private static final String DELETE_activity_participants = "delete from activity_participants where userID = ?";


    private JdbcTemplate jdbc;

    @Autowired
    public JdbcUserRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取用户的总数
     *
     * @return 用户的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(userID) from _user");
    }

    /**
     * 获取特定社团中用户的总数
     *
     * @param clubID 社团ID
     * @return 社团中用户的数量
     */
    @Override
    public long countByClubID(int clubID) {
        return jdbc.queryForInt("select count(u.userID) from _user as u,club_users as cu where cu.clubID =?  and cu.userID= u.userID",clubID);
    }

    /**
     * 创建一个新的用户
     *
     * @param user 新用户的信息
     * @return 新创建的用户
     */
    @Override
    public User save(User user) {
        jdbc.update(INSERT_USER, user.getUsername(), user.getPassword(), user.getNickname(), user.getGender(), user.getCollege());
        return user;
    }

    /**
     * 根据ID查找用户
     *
     * @param userId@return 对应ID的用户
     */
    @Override
    public User findOne(int userId) {
        User user = null;
        try {
            user = jdbc.queryForObject(SELECT_USER + " where userID=?", new UserRowMapper(), userId);
        } catch (DataAccessException e) {
        }
        return user;
    }

    /**
     * 根据用户名查找用户
     *
     * @param userName 用户名
     * @return 对应用户名的用户
     */
    @Override
    public User findByUserName(String userName) {
        User user = null;
        try {
            user = jdbc.queryForObject(SELECT_USER + " where username=?", new UserRowMapper(), userName);
        } catch (DataAccessException e) {
        }
        return user;
    }

    /**
     * 根据用户名和密码查找用户
     *
     * @param userName 用户名
     * @param password 密码
     * @return 对应用户名和密码的用户
     */
    @Override
    public User findByUserName(String userName, String password) {
        User user = null;
        try {
            user = jdbc.queryForObject(SELECT_USER + " where username=? and _password=?", new UserRowMapper(), userName, password);

        } catch (DataAccessException e) {
        }
        return user;
    }

    /**
     * 更新用户信息
     *
     * @param user 用户
     * @return 更新后的用户
     */
    @Override
    public User update(User user) {
        jdbc.update(UPDATE_USER, user.getNickname(), user.getTelephone(), user.getEmail(), user.getGender(), user.getCollege(), user.getUsername());
        return user;
    }

    /**
     * 更新用户的密码
     *
     * @param username  用户名
     * @param password  新密码
     * @param email     邮箱
     * @param nickname  昵称
     * @param telephone 电话号码
     * @return 是否更新成功
     */
    @Override
    public boolean updatePasswd(String username, String password, String email, String nickname, String telephone) {
        return jdbc.update(UPDATE_USER_PASSWD, password, email, nickname, telephone, username) != 0;
    }

    /**
     * 分页查找用户
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的用户列表
     */
    @Override
    public PaginationSupport<User> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<User>(new ArrayList<User>(0), 0);
        }

        List<User> items = jdbc.query(SELECT_USER_Pagination, new UserRowMapper(), pageSize, startIndex);
        return new PaginationSupport<User>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 删除用户
     *
     * @param username 用户名
     * @return 是否删除成功
     */
    @Override
    public boolean deleteUserByUsername(String username) {
        User user = findByUserName(username);
        if (user == null) {
            return false;
        }
        jdbc.update(DELETE_club_users, user.getUserID());
        jdbc.update(DELETE_activity_participants, user.getUserID());
        return  jdbc.update(DELETE_USER_by_username, username) != 0;
    }

    /**
     * 删除用户
     *
     * @param userID 用户ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteUserByUserID(int userID) {
        User user = findOne(userID);
        if (user == null) {
            return false;
        }
        jdbc.update(DELETE_club_users, userID);
        jdbc.update(DELETE_activity_participants, userID);
        List<Map<String, Object>> mIdList = jdbc.queryForList("delete from applicant_membershipapplication where userID=? returning membershipapplicationid", userID);
        for (Object o : mIdList){
            int id = Integer.parseInt(o.toString().split("=")[1].split("}")[0]);
            jdbc.update("delete from membershipapplication where membershipapplicationid=?", id);
        }
        return  jdbc.update(DELETE_USER_by_userID, userID) != 0;
    }

    /**
     * 根据clubID和username查找用户
     *
     * @param clubID
     * @param username
     * @return 返回所有符合的user
     */
    @Override
    public List<User> findUsersByClubIDAndUserName(int clubID, String username) {
        return jdbc.query("select u.* from _user as u,club_users as cu where u.userID = cu.userID and cu.clubID = ? and u.username = ?",new UserRowMapper(),clubID,username);
    }

    /**
     * 根据club查找用户
     *
     * @param club 社团
     * @return 社团中的用户
     */
    @Override
    public List<User> findUsersByClub(Club club) {
        return jdbc.query("select u.* from _user as u,club_users as cu where u.userID = cu.userID and cu.clubID = ?",new UserRowMapper(),club.getClubID());

    }

    /**
     * 获取所有用户
     *
     * @return 所有用户的列表
     */
    @Override
    public List<User> findAll() {
        return jdbc.query(SELECT_USER + " order by userID", new UserRowMapper());
    }

    /**
     * 判断用户是否参加某一活动
     *
     * @param userID     用户ID
     * @param activityID 活动ID
     * @return 是否参加活动
     */
    @Override
    public boolean isParticipant(int userID, int activityID) {

        return jdbc.update("select * from activity_participants where userID = ? and activityID = ?",userID, activityID) != 0;
    }


    static class UserRowMapper implements RowMapper<User> {
        /**
         * 将数据库查询结果映射到User对象
         *
         * @param rs 结果集
         * @param i 行号
         * @return User对象
         * @throws SQLException SQL异常
         */
        public User mapRow(ResultSet rs, int i) throws SQLException {
            return new User(rs.getString("username"),
                    rs.getString("_password"),
                    rs.getString("nickname"),
                    rs.getString("telephone"),
                    rs.getString("email"),
                    rs.getTimestamp("createTime"),
                    rs.getBoolean("gender"),
                    rs.getString("college"),
                    rs.getString("studentID"),
                    rs.getInt("userID"));
        }
    }
}
